<?php

/*********************************************************************************
 * Copyright (C) 2011-2013 X2Engine Inc. All Rights Reserved.
 * 
 * X2Engine Inc.
 * P.O. Box 66752
 * Scotts Valley, California 95067 USA
 * 
 * Company website: http://www.x2engine.com 
 * Community and support website: http://www.x2community.com 
 * 
 * X2Engine Inc. grants you a perpetual, non-exclusive, non-transferable license 
 * to install and use this Software for your internal business purposes.  
 * You shall not modify, distribute, license or sublicense the Software.
 * Title, ownership, and all intellectual property rights in the Software belong 
 * exclusively to X2Engine.
 * 
 * THIS SOFTWARE IS PROVIDED "AS IS" AND WITHOUT WARRANTIES OF ANY KIND, EITHER 
 * EXPRESS OR IMPLIED, INCLUDING WITHOUT LIMITATION THE IMPLIED WARRANTIES OF 
 * MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, TITLE, AND NON-INFRINGEMENT.
 ********************************************************************************/

Yii::import('application.components.util.*');

/**
 * Sample/dummy data exporter.
 * 
 * A command for exporting non-application (human-entered) data into an SQL 
 * script for use as sample data. Requires the "mysqldump" utility to be 
 * installed on the system.
 * 
 * The SQL generated by this script can be used as an alternate method for 
 * exporting data, reinstalling and importing data into the fresh installation.
 * Note, however that it does not save custom modules or any of the tables 
 * listed in $tblsExclude for these reasons:
 * 
 * - x2_auth tables: there is no easy, reliable way of distinguishing 
 * 		user-entered data in this table from default application data.
 * - x2_sessions/x2_temp_files: This data is entirely ephemeral
 * - x2_timezones/x2_timezone_points: This is static data inserted during 
 * 		installation and doesn't need to be exported.
 * 
 * Note also that any files in the uploads folder will also need to be backed up,
 * if the data is to be re-used elsewhere; references to files on the server 
 * will otherwise point to nonexistent files.
 * @package X2CRM.commands
 * @author Demitri Morgan <demitri@x2crm.com>
 */
class SampleDataCommand extends CConsoleCommand {

	public $pdo;

	/**
	 * Format a string with a value such that it can be used in an SQL statement
	 * 
	 * @param type $x
	 * @return str
	 */
	public function sqlValue($val) {
		if ($val === null)
			return "NULL";
		else if (is_int($val))
			return "$val";
		else if (is_bool($val))
			return (string) ((int) $val);
		else // string
			return $this->pdo->quote($val);
	}

	/**
	 * Exports the database content into dummy data files
	 * 
	 * @param array $args
	 * @param PDOException $e
	 * @return type 
	 */
	public function actionExport($args) {

        if (!copy ("./data/install_timestamp", "./data/dummy_data_date")) {
            die ("Error: actionExport: failed to copy install_timestamp to dummy_data_date");
        }

// [edition] => [array of table names]
		$tblEditions = require(realpath(Yii::app()->basePath . '/data/nonFreeTables.php'));
		$nonFreeEditions = require(realpath(Yii::app()->basePath . '/data/editions.php'));
		$allEditions = array_keys($tblEditions);
		$specTemplate = array_fill_keys($allEditions, array());
		$this->pdo = Yii::app()->db->pdoInstance;
		$conf = realpath(Yii::app()->basePath . '/config/X2Config.php');
		if ($conf) {
			if ((include $conf) !== 1) {
				die('Configuration import failed.');
			}
		} else {
			die("Configuration file not found. This script must be run in protected/data.\n");
		}
		$getTbls = $this->pdo->prepare("SHOW TABLES IN `$dbname`");
		$getTbls->execute();
		try {
			$allTbls = array_map(function($tr)use($dbname) {
						return $tr["Tables_in_$dbname"];
					}, $getTbls->fetchAll(PDO::FETCH_ASSOC));
		} catch (PDOException $e) {
			die("Database error: " . $e->getMessage() . "\n");
		}

		/**
		 * The command for exporting data:
		 */
		$command = "mysqldump -tc -u $user -p$pass $dbname ";

		/* $dummy_data = true; */
// Ignore pattern for lines in output of mysqldump:
		$lPat = '/^(\/\*|\-\-|\s*$';
		/* if ($dummy_data) { */
		// Export current app's data as "dummy" (usage example) data
		$lPat.='|(?:UN)?LOCK TABLES)/';
		$out = FileUtil::rpath(Yii::app()->basePath . '/data/dummy_data%s.sql');
		/* } else {
		  $lPat .= ')/';
		  $out = $argv[1];
		  if (!realpath($outDir = dirname($out)))
		  die("Error: path " . $outDir . " does not exist.\n");
		  } */

		/**
		 * Update the list of tables for each edition with the default tables:
		 */
		$nonFreeTbls = array_reduce($allEditions, function($a, $e)use($tblEditions) {
					return array_merge($tblEditions[$e], $a);
				}, array());
		$tblEditions['opensource'] = array_diff($allTbls, $nonFreeTbls);

		/*		 * **************************************** */
		/* Declare the export specification arrays */
		/*		 * **************************************** */
		/* Here it's specified what data will be exported and how.
		 * Each of these arrays follows the basic pattern of $specTemplate:
		 * [edition] => [array of table names or ([table name] =>[spec])]
		 */

		/**
		 * These will be excluded from data export altogether
		 */
		$tblsExclude = $specTemplate;
		$tblsExclude['opensource'] = array_merge(array(
			'x2_admin',
			'x2_auth_assignment',
			'x2_auth_item',
			'x2_auth_item_child',
			'x2_modules',
			'x2_sessions',
			'x2_temp_files',
			'x2_timezones',
			'x2_timezone_points',
			'x2_tips'
		),$tblEditions['pro']);
		$tblsExclude['pro'] = array(
			'x2_forwarded_email_patterns',
		);

		/**
		 * These will be included, but with specific criteria
		 */
		$tblsWhere = $specTemplate;
		$tblsWhere['opensource'] = array(
			'x2_dropdowns' => 'id>=1000',
			'x2_fields' => 'custom=1',
			'x2_form_layouts' => 'id>=1000',
			'x2_media' => '(id>11 AND id<1000) OR (id>1006 AND id<2000) OR id>2002', // Quit messing with my head, guys! I mean it! -- keep the "id" field following a simple and consistent pattern in protected/modules/media/data/install.sql
			'x2_profile' => 'id>2',
			'x2_users' => 'id>2',
			'x2_social' => 'id>1',
			'x2_docs' => 'id>52 OR id<52' // exclude the sample quote template, which is default
		);

		/**
		 * Update statements will be generated for these tables on which there's no way
		 * of inserting it at install time without running into duplicate primary key
		 * errors (because it's a record inserted by the installer itself). In each table:
		 * 'pk' =>  primary key (string for single-column or array for multi-column)
		 * 'fields' => array of fields to update or "*" to update all fields. Must include primary key.
		 * 'where' => records for which to generate update statements
		 */
		$tblsChangeDefault = $specTemplate;
		$tblsChangeDefault['opensource'] = array(
			'x2_profile' => array(
				'pk' => 'id',
				'fields' => '*',
				'where' => '`id`=1'
			),
			'x2_users' => array(
				'pk' => 'id',
				'fields' => array('id', 'firstName', 'lastName', 'officePhone', 'cellPhone', 'showCalendars', 'calendarViewPermission', 'calendarEditPermission', 'calendarFilter', 'setCalendarPermissions'),
				'where' => '`id`=1'
			)
		);

		/**
		 * Switch the order of output generation so that foreign key constraints don't 
		 * fail during insertion. List dependencies here.
		 */
		$insertFirst = $specTemplate;
		$insertFirst['opensource'] = array(
			'x2_list_criteria' => array('x2_lists'),
			'x2_list_items' => array('x2_lists'),
			'x2_role_to_workflow' => array('x2_workflow_stages', 'x2_roles', 'x2_workflows'),
			'x2_workflow_stages' => array('x2_workflows'),
            'x2_action_text' => array('x2_actions')
		);
		/**
		 * This array stores tables to be executed "next"
		 */
		$insertNext = $specTemplate;

		/**
		 * The resulting SQL to be written to files 
		 */
		$allSql = $specTemplate;

		/**
		 * Assemble the array of combined export specs.
		 * 
		 * Note that since the "where" conditions are put in the array last, they'll
		 * take precedence (so if it's listed in both $tblsExclude and $tblsWhere, 
		 * only $tblsWhere will apply).
		 */
		$allTbls = array();
		foreach ($allEditions as $edition) {
			$allTbls[$edition] = array_fill_keys($tblEditions[$edition], true);
			foreach ($tblsExclude[$edition] as $tbl)
				$allTbls[$edition][$tbl] = false;
			foreach ($tblsWhere[$edition] as $tbl => $where)
				$allTbls[$edition][$tbl] = $where;
		}

// The update statement that will be used for updating records post-insertion:
		$updateStatement = "UPDATE `%s` SET %s WHERE %s;";

		foreach ($nonFreeEditions as $edition)
			$allSql[$edition][] = "/* @edition:$edition */";

		/*		 * ************************** */
		/* Generate SQL for the data */
		/*		 * ************************** */
		foreach ($allTbls as $edition => $tbls) {

			/**
			 * Generate insertion statements 
			 */
			$eTbls = $tbls;
			while (count($eTbls) > 0) {
				$tblsTmp = $eTbls;
				foreach ($tblsTmp as $tbl => $where) {
					if ($where != false) {
						// This table is to be included in the data export
						if (array_key_exists($tbl, $insertFirst[$edition])) {
							// This table depends on other tables being ready with data
							$skip = False;
							foreach ($insertFirst[$edition][$tbl] as $tblFirst)
							// Check to see if the table has been accounted for already
								if (array_key_exists($tblFirst, $eTbls)) {
									$skip = True;
									break;
								}
							if ($skip)
							// Not all dependencies of this table have been resolved yet.
								continue;
						}
						$output = array();
						$tblCommand = "$command $tbl" . ($where !== true ? " --where='" . $where . "' " : ' ');
						exec($tblCommand, $output);
						foreach ($output as $line) {
							if (!preg_match($lPat, $line)) {
								$allSql[$edition][] = $line;
							}
						}
					}
					unset($eTbls[$tbl]);
				}
			}

			/**
			 * Generate update statements 
			 */
			foreach ($tblsChangeDefault[$edition] as $tbl => $how) {
				$colSel = $how['fields'];
				if (is_array($how['fields']))
					$colSel = '`' . implode('`,`', $how['fields']) . '`';
				$query = $this->pdo->prepare("SELECT $colSel FROM `$tbl` WHERE {$how['where']}");
				$query->execute();
				$recs = $query->fetchAll(PDO::FETCH_ASSOC);
				$pk = $how['pk'];
				if (!is_array($pk))
					$pk = array($pk);
				foreach ($recs as $rec) {
					// Generate a "where" clause criterion to refer to this record by its primary key
					$whereSelector = array();
					foreach ($pk as $c) {
						$whereSelector[] = "`$c`=" . $this->sqlValue($rec[$c]);
					}
					// Exclude the primary key from the columns to be updated:
					foreach ($pk as $col)
						unset($rec[$col]);
					$fieldsSet = array();

					foreach ($rec as $col => $val)
						$fieldsSet[] = "`$col`=" . $this->sqlValue($val);

					$allSql[$edition][] = sprintf($updateStatement, $tbl, implode(',', $fieldsSet), implode(' AND ', $whereSelector));
				}
			}
		}

		/* if ($dummy_data) { */
		// Create dummy data files
		foreach ($allSql as $edition => $sqls)
			file_put_contents(sprintf($out, $edition == 'opensource' ? '' : "-$edition"), implode("\n/*&*/\n", $sqls));
		/* } else {
		  // Put it all in the same file
		  $allOut = array();
		  foreach ($allSql as $edition => $sqls)
		  foreach ($sqls as $sql)
		  $allOut[] = $sql;
		  file_put_contents($out, implode("\n", $allOut));
		  } */
	}

	/**
	 * Hunts through the database for in-the-future timestamps and reports them
	 *
	 * @param type $args 
	 */
	public function actionFutureTimes($args) {
		$dateFields = require(realpath(Yii::app()->basePath . '/data/dateFields.php'));
		$maxFuture = array(
			'table' => null,
			'column' => null,
			'key' => null,
			'date' => 0
		);
		$useFile = array_pop($args);
		if($useFile)
			$time = (int) file_get_contents(realpath(Yii::app()->basePath.'/data/dummy_data_date'));
		else
			$time = time();
		$minFuture = array_merge(array(),$maxFuture);
		$minFuture['date'] = PHP_INT_MAX;
		$time = time();
		$futureFields = require(realpath(Yii::app()->basePath.'/data/futureFields.php'));
		$futureTables = array_keys($futureFields);

		foreach ($dateFields as $table => $cols) {
			$pk = Yii::app()->db->schema->getTable($table)->primaryKey;
			
			$pastCols = $cols;
			// Exclude fields that are permitted to be in the future:
			if(in_array($table,$futureTables))
				$pastCols = array_diff($pastCols,$futureFields[$table]);

			$select = array_merge(is_array($pk) ? $pk : array($pk), $pastCols);
			$where = '`' . implode("`>$time OR `", $pastCols) . "`>$time";
			$dates = Yii::app()->db->createCommand()->select($select)->from($table)->where($where)->queryAll();
			if(!empty($dates))
				echo implode("\t", $pastCols) . "\t($table)\n";
			foreach ($dates as $record) {
				$line = '';
				foreach ($select as $col) {
					$line .= ($record[$col] == null ? "NULL" : $record[$col]) . "\t";
				}
				foreach ($pastCols as $dateField) {
					$date = $record[$dateField];
					if ($date > $maxFuture['date']) {
						$maxFuture['table'] = $table;
						$maxFuture['column'] = $dateField;
						$maxFuture['key'] = var_export($pk, true);
						$maxFuture['date'] = $date;
					}
					if ($date > $time && $date < $minFuture['date']) {
						$minFuture['table'] = $table;
						$minFuture['column'] = $dateField;
						$minFuture['key'] = var_export($pk, true);
						$minFuture['date'] = $date;
					}
				}
				echo "$line\n";
			}
		}
		echo "\nRecord furthest in the future:\n";
		print_r($maxFuture);
		echo strftime('%c',$maxFuture['date']);
		echo "\nRecord least far in the future:\n";
		print_r($minFuture);
		echo strftime('%c',$minFuture['date']);

	}

}

?>
